package com.jiuqi.bi.database.db2.metadata;

import com.jiuqi.bi.database.metadata.*;
import com.jiuqi.bi.sql.DataTypes;
import com.jiuqi.bi.util.StringUtils;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DB2Metadata extends DefaultSQLMetadata {
	private static final String SQL_QUERYFOREIGN_BY_SCHEMA_NAME = "select constname as foreignkeyname, tabname as subtablename, fk_colnames as subcolumntable,reftabname as maintablename,pk_colnames as maincolumntable,COLCOUNT from syscat.references where UPPER(owner) = UPPER(?)";
	private static final String SQL_QUERYFOREIGN_BY_TABLE_NAME = "select constname as foreignkeyname, tabname as subtablename, fk_colnames as subcolumntable,reftabname as maintablename,pk_colnames as maincolumntable,COLCOUNT from syscat.references where UPPER(owner) = UPPER(?) AND UPPER(tabname) = UPPER(?)";

	public DB2Metadata(Connection conn) {
		super(conn);
	}
	
	@Override
	public String getDefaultSchema() throws SQLException {
		PreparedStatement ps = conn.prepareStatement(SQL_QUERY_SCHEMA);
		try {
			ResultSet rs = ps.executeQuery();
			try {
				if(rs.next())
					return rs.getString(1);
			} finally {
				rs.close();
			}

			return conn.getMetaData().getUserName();
		} finally {
			ps.close();
		}
	}

	/**
	 * 根据【用户命名空间】查询外键信息
	 * @param schName  用户命名空间
	 * @return
	 * @throws SQLException
	 * @throws SQLMetadataException
	 */
	@Override
	public List<LogicForeignKey> getLogicForeignKeyBySchemaName(String schName) throws SQLException, SQLMetadataException {
		String schemaName = schName;
		if (schemaName == null || schemaName.equals("")) {
			schemaName = conn.getMetaData().getUserName();
		}
		PreparedStatement ps = conn.prepareStatement(SQL_QUERYFOREIGN_BY_SCHEMA_NAME);
		try {
			ps.setString(1, schemaName);
			ResultSet rs = ps.executeQuery();
			return processLogicForeignKeySearchResult(rs);
		} finally {
			ps.close();
		}
	}

	/**
	 * 根据【默认用户命名空间】和【指定表名】查询外键信息
	 * @param tableName 指定表名
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<LogicForeignKey> getLogicForeignKeyByTableName(String tableName) throws SQLException {
		String schemaName = conn.getMetaData().getUserName();
		List<LogicForeignKey> logicForeignKeyByTableName = getLogicForeignKeyByTableName(schemaName, tableName);
		return logicForeignKeyByTableName;
	}


	/**
	 * 根据【指定用户命名空间】和【指定表名】查询外键信息
	 * @param schName 用户命名空间
	 * @param tableName  指定表名
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<LogicForeignKey> getLogicForeignKeyByTableName(String schName, String tableName) throws SQLException {
		String schemaName = schName;
		if (schemaName == null || schemaName.equals("")) {
			schemaName = conn.getMetaData().getUserName();
		}
		PreparedStatement ps = conn.prepareStatement(SQL_QUERYFOREIGN_BY_TABLE_NAME);
		try {
			ps.setString(1, schemaName);
			ps.setString(2,tableName);
			ResultSet rs = ps.executeQuery();
			return processLogicForeignKeySearchResult(rs);
		} finally {
			ps.close();
		}
	}

	/**
	 * 处理查询结果
	 * @param rs  jdbc查询结果
	 * @return
	 * @throws SQLException
	 */
	private List<LogicForeignKey> processLogicForeignKeySearchResult(ResultSet rs) throws SQLException {
		try {
			//最终返回对象
			List<LogicForeignKey> logicForeignKeyList = new ArrayList<>();
			while (rs.next()) {
				String foreignKeyName = rs.getString("foreignkeyname");
				String subTableName = rs.getString("subtablename");
				String subColumnName = rs.getString("subcolumntable").trim();
				String mainTableName = rs.getString("maintablename");
				String mainColumnName = rs.getString("maincolumntable").trim();
				int colcount = rs.getInt("COLCOUNT");


				//从表列名、主表列名
				List<String> subColumnNameList = new ArrayList<>();
				List<String> mainColumnNameList = new ArrayList<>();

				//普通外键类型，直接添加到过程对象中
				if (colcount==1) {

					subColumnNameList.add(subColumnName);
					mainColumnNameList.add(mainColumnName);

					LogicForeignKey logicForeignKey = new LogicForeignKey(foreignKeyName,subTableName,subColumnNameList,mainTableName,mainColumnNameList);

					logicForeignKeyList.add(logicForeignKey);

				}else{//联合外键类型，需要将联合外键的字段对应关系以集合形式表示
					Arrays.asList(subColumnName.split(" ")).stream().filter(item->{
						return !"".equals(item);
					}).forEach(ite->{
						subColumnNameList.add(ite);
					});

					Arrays.asList(mainColumnName.split(" ")).stream().filter(item->{
						return !"".equals(item);
					}).forEach(ite->{
						mainColumnNameList.add(ite);
					});

					LogicForeignKey anotherlogicForeignKey = new LogicForeignKey(foreignKeyName,subTableName,subColumnNameList,mainTableName,mainColumnNameList);

					logicForeignKeyList.add(anotherlogicForeignKey);
				}
			}
			return logicForeignKeyList;
		} finally {
			rs.close();
		}
	}


}
